Skip Headers

Oracle® Database Java Developer's Guide 10g Release 2 (10.2) Part Number B14187-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 12 Database Web Services

This chapter provides an overview of database Web services and discusses how to call existing Web services. This chapter contains the following sections:

- Overview of Database Web Services

- Using Oracle Database as Service Provider for Web Services

- Using Oracle Database as Service Consumer for Web Services

## 12.1 Overview of Database Web Services

Web services enable application-to-application interaction over the Web, regardless of platform, language, or data formats. The key ingredients, including Extensible Markup Language (XML), Simple Object Access Protocol (SOAP), Web Services Description Language (WSDL), and Universal Description, Discovery, and Integration (UDDI), have been adopted across the entire software industry. Web services usually refers to services implemented and deployed in middle-tier application servers. However, in heterogeneous and disconnected environments, there is an increasing need to access stored procedures, as well as data and metadata, through Web services interfaces.

The Database Web services technology is a database approach to Web services. It works in the following two directions:

- Accessing database resources as a Web service

- Consuming external Web services from the database

Oracle Database can access Web services through PL/SQL packages and Java classes deployed within the database. Turning Oracle Database into a Web service provider leverages investment in Java stored procedures, PL/SQL packages, predefined SQL queries, and data manipulation language (DML). Conversely, consuming external Web services from the database, together with integration with the SQL engine, enables Enterprise Information Integration.

## 12.2 Using Oracle Database as Service Provider for Web Services

Web Services use industry-standard mechanisms to provide easy access to remote content and applications, regardless of the platform and location of the provider and implementation and data format. Client applications can query and retrieve data from Oracle Database and call stored procedures using standard Web service protocols. There is no dependency on Oracle-specific database connectivity protocols. This approach is highly beneficial in heterogeneous, distributed, and disconnected environments.

You can call into the database from a Web service, using the database as a service provider. This enables you to leverage existing or new SQL, PL/SQL, Java stored procedures, or Java classes within Oracle Database. You can access and manipulate database tables from a Web service client.

This section covers the following topics:

- How to Use JPublisher for Web Services Call-Ins

- Features of Oracle Database as a Web Service Provider

- JPublisher Support for Web Services Call-Ins to Oracle Database

### 12.2.1 How to Use JPublisher for Web Services Call-Ins

You can use JPublisher to generate Java wrappers that correspond to database operations and deploy the wrappers as Web services in Oracle Application Server. Figure 12-1 illustrates how you use JPublisher to publish PL/SQL packages, SQL objects, collections, and packages as Java classes. Once published, these classes can be accessed by any Web service through an Oracle Application Server Containers for J2EE (OC4J) Web services servlet.

Figure 12-1 Web Services Call-In to the Database

Description of the illustration webservices_calling_database.gif

See Also: Oracle Database JPublisher User's Guide

### 12.2.2 Features of Oracle Database as a Web Service Provider

Using Oracle Database as a Web service provider offers the following features:

- Enhances PL/SQL Web services

  Improves PL/SQL Web services by extending the Web services support for additional PL/SQL types, including `CLOB`, `BLOB`, `XMLTYPE`, `REF CURSOR`, and PL/SQL records and tables. This enables you to use most of your existing PL/SQL packages as Web services.

- Exposes Java in the database as Web services

  Exposes existing Java classes deployed in Oracle Database as Web services. Java classes implementing data-related services can be migrated between the middle tier and the database. Java portability results in database independence.

- Provides SQL query Web services

  Leverages warehousing or business intelligence queries, data monitoring queries, and any predefined SQL statements as Web services.

- Enables DML Web services

  Offers secure, persistent, transactional, and scalable logging, auditing, and tracking operations implemented through SQL DML, as Web services. DML Web services are implemented as atomic or group, or batch, `INSERT`, `UPDATE`, and `DELETE` operations.

### 12.2.3 JPublisher Support for Web Services Call-Ins to Oracle Database

The following JPublisher features support Web services call-ins to the code running in Oracle Database:

- Generation of Java interfaces

- JPublisher styles and style files

- `REF CURSOR` returning and result set mapping

- Options to filter what JPublisher publishes

- Support for calling Java classes in the database without PL/SQL call specifications

- Support for publishing SQL queries or DML statements

- Support for unique method names

- Support for Oracle Streams AQ

See Also: Oracle Database JPublisher User's Guide

## 12.3 Using Oracle Database as Service Consumer for Web Services

You can extend the storage, indexing, and searching capabilities of a relational database to include semistructured and nonstructured data, including Web services, in addition to enabling federated data. By calling Web services, the database can track, aggregate, refresh, and query dynamic data produced on-demand, such as stock prices, currency exchange rates, and weather information.

An example of using Oracle Database as a service consumer would be to call external Web services from a predefined database job to retrieve inventory information from multiple suppliers, and then update your local inventory database. Another example is that of a Web crawler, where a database job can be scheduled to collate product and price information from a number of sources.

This section covers the following topics:

- How to Use Oracle Database for Web Services Call-Outs

- Web Service Data Sources (Virtual Table Support)

- Features of Oracle Database as a Web Service Consumer

- JPublisher Generation Overview

- Adjusting the Mapping of SQL Types

### 12.3.1 How to Use Oracle Database for Web Services Call-Outs

The Web services client code is written in SQL, PL/SQL, or Java to run inside Oracle Database, which then calls the external Web service. Figure 12-2 illustrates how you can call a Web service from a Java client within the database, using one of the following methods:

- SQL and PL/SQL call specifications

  Start a Web service through a user-defined function call, which is generated through JPublisher, either directly within a SQL statement or view or through a variable.

- Pure Java `static` proxy class

  Use JPublisher to generate a client proxy class, which uses Java API for XML-based remote procedure call (JAX-RPC). This method simplifies the Web service invocation because the location of the service is already known without needing to look up the service in the UDDI registry. The client proxy class does all the work required to construct the SOAP request, including marshalling and unmarshalling parameters.

- Pure Java using dynamic invocation interface (DII) over JAX-RPC

  Dynamic invocation provides the ability to construct the SOAP request and access the service without the client proxy.

For Web services call-outs using PL/SQL, use the `UTL_DBWS` PL/SQL package. This package essentially uses the same application programming interfaces (APIs) as the DII classes.

You can use a Web services data source to process the results from any Web service request.

Figure 12-2 Calling Web Services From Within the Database

Description of the illustration webservices_from_database.gif

### 12.3.2 Web Service Data Sources (Virtual Table Support)

To access data that is returned from single or multiple Web service invocations, create a virtual table using a Web service data source. This table lets you query a set of returned rows as though it were a table.

The client calls a Web service and the results are stored in a virtual table in the database. You can pass result sets from function to function. This enables you to set up a sequence of transformation without a table holding intermediate results. To reduce memory usage, you can return the result set rows, a few at a time, within a function.

By using Web services with the table function, you can manipulate a range of input values from single or multiple Web services as a real table. In the following example, the inner `SELECT` statement creates rows whose columns are used as arguments for calling the `CALL_WS` Web service call-out.

    SELECT column1, cloumn2, ...
    FROM TABLE(WS_TABFUN(CURSOR(SELECT s FROM table_name)))
    WHERE ...

The table expression in the preceding example can be used in other SQL queries, for constructing views, and so on.

Figure 12-3 illustrates the support for virtual table.

Figure 12-3 Storing Results from Request in a Virtual Table

Description of the illustration storing_results_virtual.gif

### 12.3.3 Features of Oracle Database as a Web Service Consumer

Using Oracle Database as a Web service consumer provides the following features:

- Consuming Web services form Java

  Provides an easy-to-use interface for Web services call-outs, thereby insulating developers from low-level SOAP programming. Java classes running in the database can directly call external Web services by using the previously loaded Java proxy class or through dynamic invocation.

- Consuming Web services from SQL and PL/SQL

  Enables any SQL-enabled tool or application to transparently and easily consume dynamic data from external Web services. After exposing Web services methods as Java stored procedures, a PL/SQL wrapper on top of a Java stored procedure hides all Java and SOAP programming details from the SQL client.

- Using Web services data source

  Enables application and data integration by turning external Web service into a SQL data source, making the external Web service appear as regular SQL table. This table function represents the output of calling external Web services and can be used in a SQL query.

### 12.3.4 JPublisher Generation Overview

JPublisher can receive the WSDL file from a Web service and create the static Java, proxy class, or PL/SQL call specification, as shown in Figure 12-4.

Figure 12-4 Creating Web Services Call-Out Stubs

Description of the illustration creating_stubs.gif

This support is created through the following JPublisher key options:

- `-proxywsdl=``url`

  Use this option to generate JAX-RPC static client proxies, given the WSDL document at the specified URL. This option generates additional wrapper classes to expose instance methods as `static` methods and generates PL/SQL wrappers. It performs the following steps:

  1. Generates JAX-RPC client proxy classes.

  2. Generates wrapper classes to publish instance methods as `static` methods.

  3. Generates PL/SQL wrappers for classes that must be accessible from PL/SQL.

  4. Loads generated code into the database.

  Note: The `-proxywsdl` option uses the `-proxyclasses` option behind the scenes for steps 2 and 3, and takes the `-proxyopts` setting as input.

  Once generated, your database client can access the Web service through PL/SQL using the call specifications or through the JAX-RPC client proxy classes. The PL/SQL wrappers use the `static` methods. A client would not normally access any Web service using the `static` method directly.

- `-httpproxy=``proxy_url`

  Where WSDL is accessed through a firewall, use this option to specify a proxy URL to use in resolving the URL of the WSDL document.

- `-proxyclasses=``class_list`

  For Web services, this option is used behind the scenes by the `-proxywsdl` option and is set automatically, as appropriate. In addition, you can use this option directly, for general purposes, any time you want to create PL/SQL wrappers for Java classes with `static` methods, and optionally to produce wrapper classes to expose instance methods as `static` methods.

  The `-proxyclasses` option accepts the `-proxyopts` setting as input.

- `-proxyopts=``wrapper_specifications`

  This option specifies JPublisher behavior in generating wrapper classes and PL/SQL wrappers, usually, but not necessarily, for Web services. For typical usage of the `-proxywsdl` option, the `-proxyopts` default setting is sufficient. In situations where you use the `-proxyclasses` option directly, you may want to use the special `-proxyopts` settings.

- `-endpoint=``Web_services_endpoint`

  Use this option in conjunction with the `-proxywsdl` option to specify the Web services endpoint.

- `-sysuser=``superuser_name/superuser_password`

  Use this option to specify the name and password for the superuser account used to grant permissions for the client proxy classes to access Web services using HTTP.

### 12.3.5 Adjusting the Mapping of SQL Types

Although Oracle Application Server does not currently support `LOB` types, `XMLTYPE`, `REF CURSORS`, and `OUT` and `IN OUT` arguments, you can use an alternative approach to expose PL/SQL methods and SQL types as Web services.

You can change the default action of JPublisher to generate code that uses a user-provided subclass. For example, if you have a PL/SQL method that returns a `REF CURSOR`, then JPublisher automatically maps the return type to `java.sql.ResultSet`. However, this `ResultSet` type cannot be published as a Web service. To solve this, create a new method that can return the result set in a Web service-supported format, as follows:

    public String [] readRefCursorArray(String arg1, Integer arg2)
    {
      java.sql.ResultSet rs = getRefCursor(arg1,arg2);
      ...
      //create a String[] from rs and return it
      ...
    }

After creating a method, create an interface that contains the exact methods to publish. You can use JPublisher to easily accomplish this mapping by using the following command:

    jpub -sql=MYAPP:MyAppBase:MyApp#MyAppInterf...

In the preceding command:

- `MyApp` contains the method to return the result set.

- `MyAppInterf` is the interface that contains the method to publish.

After translating the code for your application, archive all the class files into a single Java Archive (JAR) file and use the Web Services Assembler to create a deployable Web service Enterprise Archive (EAR) file.

See Also: Oracle Database JPublisher User's Guide